﻿using MXComponent.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace MXComponent.DAL
{
    public partial class formuladetailDB
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public formuladetailDB()
        {
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">model</param>
        public int AddRecord(formuladetailData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("set nocount on; ");
            strSql.Append("insert into formuladetail(");
            strSql.Append(@"formulaid,warehouseid,planqty,errorqty,createtime,creatby) ");
            strSql.Append(" values (");
            strSql.Append(@"@formulaid,@warehouseid,@planqty,@errorqty,@createtime,@creatby)");
            strSql.Append("; select @@identity; set nocount off; ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaid", SqlDbType.Int),
                    new SqlParameter("@warehouseid", SqlDbType.Int),
                    new SqlParameter("@planqty", SqlDbType.Int),
                    new SqlParameter("@errorqty", SqlDbType.Int),
                    new SqlParameter("@createtime", SqlDbType.DateTime),
                    new SqlParameter("@creatby", SqlDbType.Int)
                };
            parameters[0].Value = model.formulaid;
            parameters[1].Value = model.warehouseid;
            parameters[2].Value = model.planqty;
            parameters[3].Value = model.errorqty;
            parameters[4].Value = model.createtime == string.Empty ? null : model.createtime;
            parameters[5].Value = model.creatby;

            int id = 0;
            object ret = SqlHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);

            if (ret != null && ret != DBNull.Value)
            {
                id = int.Parse(ret.ToString());
            }
            return id;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">model</param>
        public void ModifyRecord(formuladetailData model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            strSql.Append("update formuladetail set ");

            Dictionary<string, SqlParameter> dic = new Dictionary<string, SqlParameter>();
            dic.Add("formuladetailid", new SqlParameter("@formuladetailid", SqlDbType.Int));

            if (model.formulaid != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", formulaid=@formulaid");
                }
                else
                {
                    strSql1.Append("formulaid=@formulaid");
                }
                dic.Add("formulaid", new SqlParameter("@formulaid", SqlDbType.Int));
            }
            if (model.warehouseid != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", warehouseid=@warehouseid");
                }
                else
                {
                    strSql1.Append("warehouseid=@warehouseid");
                }
                dic.Add("warehouseid", new SqlParameter("@warehouseid", SqlDbType.Int));
            }
            if (model.planqty != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", planqty=@planqty");
                }
                else
                {
                    strSql1.Append("planqty=@planqty");
                }
                dic.Add("planqty", new SqlParameter("@planqty", SqlDbType.Int));
            }
            if (model.errorqty != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", errorqty=@errorqty");
                }
                else
                {
                    strSql1.Append("errorqty=@errorqty");
                }
                dic.Add("errorqty", new SqlParameter("@errorqty", SqlDbType.Int));
            }
           
            if (model.updatetime != null && model.updatetime != string.Empty)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", updatetime=@updatetime");
                }
                else
                {
                    strSql1.Append("updatetime=@updatetime");
                }
                dic.Add("updatetime", new SqlParameter("@updatetime", SqlDbType.DateTime));
            }
            if (model.updateby != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", updateby=@updateby");
                }
                else
                {
                    strSql1.Append("updateby=@updateby");
                }
                dic.Add("updateby", new SqlParameter("@updateby", SqlDbType.Int));
            }

            strSql.Append(strSql1);
            strSql.Append(" where formuladetailid = @formuladetailid ");

            SqlParameter[] parameters = null;

            if (dic.Count > 0)
            {
                parameters = new SqlParameter[dic.Count];

                int i = 0;
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                foreach (KeyValuePair<string, SqlParameter> obj in dic)
                {
                    parameters[i] = obj.Value;
                    PropertyInfo propinfo = model.GetType().GetProperty(obj.Key, flag);
                    //if (propinfo.PropertyType.FullName.ToUpper().CompareTo("SYSTEM.GUID") == 0)
                    //{
                    //    if ((Guid)propinfo.GetValue(model, null) == Guid.Empty)
                    //    {
                    //        parameters[i].Value = DBNull.Value;
                    //    }
                    //    else
                    //    {
                    //        parameters[i].Value = propinfo.GetValue(model, null);
                    //    }
                    //}
                    //else
                    //{
                    //    parameters[i].Value = propinfo.GetValue(model, null);
                    //}
                    parameters[i].Value = propinfo.GetValue(model, null);
                    i++;
                }
            }

            SqlHelper.ExecteNonQuery( CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="formuladetailid">主键值</param>
        public void DeleteRecord(int formuladetailid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete formuladetail");
            strSql.Append(" where formuladetailid = @formuladetailid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formuladetailid", SqlDbType.Int)
                };
            parameters[0].Value = formuladetailid;

            SqlHelper.ExecteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        public void DeleteRecordByformulaid(int formulaid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete formuladetail");
            strSql.Append(" where formulaid = @formulaid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaid", SqlDbType.Int)
                };
            parameters[0].Value = formulaid;

            SqlHelper.ExecteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个model
        /// </summary>
        /// <param name="formuladetailid">主键值</param>
        /// <returns>model</returns>
        public formuladetailData GetModel(int formuladetailid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select formuladetailid,formulaid,warehouseid,planqty,errorqty,createtime,creatby,updatetime,updateby from formuladetail");
            strSql.Append(" where formuladetailid = @formuladetailid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formuladetailid", SqlDbType.Int)
                };
            parameters[0].Value = formuladetailid;

            formuladetailData model = new formuladetailData();
            DataSet ds = SqlHelper.ExecuteDataSet( CommandType.Text, strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                if (row["formuladetailid"] != DBNull.Value)
                {
                    model.formuladetailid = Convert.ToInt32(row["formuladetailid"].ToString());
                }
                if (row["formulaid"] != DBNull.Value)
                {
                    model.formulaid = Convert.ToInt32(row["formulaid"].ToString());
                }
                if (row["warehouseid"] != DBNull.Value)
                {
                    model.warehouseid = Convert.ToInt32(row["warehouseid"].ToString());
                }
                if (row["planqty"] != DBNull.Value)
                {
                    model.planqty = Convert.ToInt32(row["planqty"].ToString());
                }
                if (row["errorqty"] != DBNull.Value)
                {
                    model.errorqty = Convert.ToInt32(row["errorqty"].ToString());
                }
                if (row["createtime"] != DBNull.Value)
                {
                    model.createtime = Convert.ToString(row["createtime"].ToString());
                }
                if (row["creatby"] != DBNull.Value)
                {
                    model.creatby = Convert.ToInt32(row["creatby"].ToString());
                }
                if (row["updatetime"] != DBNull.Value)
                {
                    model.updatetime = Convert.ToString(row["updatetime"].ToString());
                }
                if (row["updateby"] != DBNull.Value)
                {
                    model.updateby = Convert.ToInt32(row["updateby"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">限制条件</param>
        /// <returns>DataSet</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select formuladetailid,formulaid,warehouseid,planqty,errorqty,createtime,creatby,updatetime,updateby from formuladetail");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by formuladetailid");
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">限制条件</param>
        /// <returns>DataSet</returns>
        public DataSet GetViewList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select d.formuladetailid,d.formulaid,f.formulaname,d.warehouseid,H.warehousename,H.warehouseno,H.materialid,M.materialname,d.planqty,0 as actualplanqty,d.errorqty,0 as actualerrorqty,H.planqtyplc,H.errorqtyplc,H.realqtyplc,H.saveflagplc,0 as currentcount from formuladetail d
            left join formula f on d.formulaid=f.formulaid
            left join materialwarehouse H  on d.warehouseid=H.warehouseid
            left join material M on H.materialid=M.materialid
            ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(@" order by 
            CASE
            WHEN H.warehouseno = 13 THEN 0
            WHEN H.warehouseno = 14 THEN 0

            WHEN H.warehouseno = 15 THEN 0

            WHEN H.warehouseno = 16 THEN 0
            ELSE 1
            END, d.formuladetailid");
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
    }
}